package com.wanczy.dao.impl;

import java.sql.Connection;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.wanczy.dao.MenuOpDAO;
import com.wanczy.pojo.MenuTable;

public class MenuOpDAOImpl implements MenuOpDAO {
	public Connection conn = null;
	public MenuOpDAOImpl(Connection conn) {
		this.conn =conn;
	}
	/**
	 * �˵��Ĳ�ѯ����
	 */
	@Override
	public List<MenuTable> findByMenuName(String menuName,int menuState) {
		List<MenuTable> list = null;
		try {
			String sql = "select menu_id, menu_name, menu_describe, menu_state, menu_url, menu_level from menu_table where menu_name like ? and menu_state=? ";
			QueryRunner qRunner = new QueryRunner();
			Object param[] = {"%"+menuName+"%",menuState};
			list = qRunner.query(this.conn, sql, new BeanListHandler<>(MenuTable.class),param);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
		
	}
	@Override
	public int findCountByMenuName(int userId) {
		int count = 0;
		List<MenuTable> list = null;
		try {
			String sql = "select distinct b.menu_id from role_menu_table b where b.role_id in (select a.role_id from user_role_table a where a.user_id = ?)";
			QueryRunner qRunner = new QueryRunner();
			Object param[] = {userId};
			list = qRunner.query(this.conn, sql, new BeanListHandler<>(MenuTable.class),param);
			count = list.size();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	/**
	 * �˵���αɾ������
	 */
	@Override
	public boolean doDel(int menuId) {
		boolean flag = true;
		try {
			String sql = "update menu_table set menu_state = 0 where menu_id = ?";
			QueryRunner qRunner = new QueryRunner();
			Object param[] = {menuId};
			int x = qRunner.update(this.conn,sql,param);
			if(x == 0) {
				flag = false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * ��Ӳ˵���Ϣ
	 */
	@Override
	public boolean doIns(MenuTable menuTble) {
		boolean flag = true;
		try {
			String sql = "insert into menu_table(menu_id,menu_name,menu_describe, menu_state, menu_url, menu_level) values(?,?,?,?,?,?)";
			QueryRunner qRunner = new QueryRunner();
			Object param[] = {menuTble.getMenu_id(),menuTble.getMenu_name(),menuTble.getMenu_describe(),menuTble.getMenu_state(),menuTble.getMenu_url(),menuTble.getMenu_level()};
			int x = qRunner.update(this.conn,sql,param);
			if(x == 0) {
				flag = false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * ���ݲ˵�Id�����������ݵĲ�ѯ
	 */
	@Override
	public MenuTable findByMenuId(int menuId) {
		MenuTable menuTable = null;
		try {
			String sql = "select menu_id, menu_name, menu_describe, menu_state, menu_url, menu_level from menu_table where menu_id = ?";
			QueryRunner qRunner = new QueryRunner();
			Object param[] = {menuId};
			List<MenuTable> list = qRunner.query(this.conn,sql,new BeanListHandler<>(MenuTable.class),param);
			menuTable = list.get(0);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return menuTable;
	}
	/**
	 * �����˵��Ĳ�ѯ����
	 */
	@Override
	public List<MenuTable> findByNextMenu(String menuLevel) {
		List<MenuTable> list = null;
		try {
			String sql = "select menu_id, menu_name, menu_describe, menu_state, menu_url, menu_level from menu_table where menu_level like ? and length(menu_level)=6";
			Object param[] = {menuLevel+"%"};
			QueryRunner qRunner = new QueryRunner();
			list = qRunner.query(this.conn, sql, new BeanListHandler<>(MenuTable.class),param);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * �޸Ĳ˵���Ϣ
	 */
	@Override
	public boolean doUpd(MenuTable menuTable) {
		boolean flag = true;
		try {
			String sql = "update menu_table set menu_name = ?,menu_describe = ?, menu_state = ?, menu_url = ?, menu_level = ? where menu_id = ?";
			QueryRunner qRunner = new QueryRunner();
			Object param[] = {menuTable.getMenu_name(),menuTable.getMenu_describe(),menuTable.getMenu_state(),menuTable.getMenu_url(),menuTable.getMenu_level(),menuTable.getMenu_id()};
			int x = qRunner.update(this.conn,sql,param);
			if(x == 0) {
				flag = false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	/**
	 * ��ѯ��һ���˵�����
	 */
	@Override
	public List<MenuTable> findByMenuLevel(int menuLevel) {
		List<MenuTable> list = null;
		try {
			String sql = "select menu_id, menu_name, menu_describe, menu_state, menu_url, menu_level from menu_table where length(menu_level)=?";
			Object param[] = {menuLevel};
			QueryRunner qRunner = new QueryRunner();
			list = qRunner.query(this.conn, sql, new BeanListHandler<>(MenuTable.class),param);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * ��ѯ�˵��ȼ�
	 */
	@Override
	public String findByMenuName(String menuName) {
		List<MenuTable> list = null;
		String menuLevel = "";
		try {
			String sql = "select menu_id, menu_name, menu_describe, menu_state, menu_url, menu_level from menu_table where menu_name = ?";
			Object param[] = {menuName};
			QueryRunner qRunner = new QueryRunner();
			list = qRunner.query(this.conn, sql, new BeanListHandler<>(MenuTable.class),param);
			menuLevel = list.get(0).getMenu_level();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return menuLevel;
	}
	@Override
	public List<MenuTable> display(int userId, int pageSize, int pageCurrent,String menuName) {
		List<MenuTable> list = null;
		try {
			String sql = "select distinct menu_id, menu_name, menu_describe, menu_state, menu_url, menu_level " + 
					"from (select menu_id, menu_name, menu_describe, menu_state, menu_url, menu_level,rownum rn " + 
					"from menu_table where menu_id in(select menu_id from role_menu_table  where role_id in (select role_id from user_role_table  where user_id=?)) and menu_name like ? ) " + 
					"where rn > ? and rn < = ? order by menu_id";
			Object param[] = {userId,"%"+menuName+"%",(pageCurrent-1)*pageSize,pageCurrent*pageSize};
			QueryRunner qRunner = new QueryRunner();
			list = qRunner.query(this.conn, sql, new BeanListHandler<>(MenuTable.class),param);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

}

